from sqlalchemy import or_, exc

from app.model.SysUserModel import SysUserModel
from app.config.db import DBUtils


class SysUserService:

    def __init__(self):
        self.db_utils = DBUtils()

    # 查询全部
    def select_alldata(self, session, pageNum, pageSize):
        try:
            if pageNum is not None and pageSize is not None:
                list_sys_user = session.query(SysUserModel).order_by(SysUserModel.CREATE_TIME.desc()).limit(pageSize).offset(
                    (pageNum - 1) * pageSize).all()
            else:
                list_sys_user = session.query(SysUserModel).order_by(SysUserModel.CREATE_TIME.desc()).all()
            total_count = session.query(SysUserModel).count()
            sys_users = [item.to_all_dict() for item in list_sys_user]
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": sys_users
            }
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 数据详情，根据ID获取数据
    def select_data_by_uid(self, session, uid):
        try:
            sys_user = session.query(SysUserModel).filter(SysUserModel.ID == uid).first()
            return sys_user.to_all_dict() if sys_user is not None else None
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 模糊查询
    def select_blur_data(self, session, pageNum, pageSize, condition):
        try:
            if condition is None:
                list_sys_user = session.query(SysUserModel).order_by(SysUserModel.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(SysUserModel).count()
            else:
                list_sys_user = session.query(SysUserModel).filter(
                    or_(
                        SysUserModel.USERNAME.like(f"%{condition}%"),
                        SysUserModel.NICKNAME.like(f"%{condition}%"),
                    )
                ).order_by(SysUserModel.CREATE_TIME.desc()).limit(pageSize).offset((pageNum - 1) * pageSize).all()
                total_count = session.query(SysUserModel).filter(
                    or_(
                        SysUserModel.USERNAME.like(f"%{condition}%"),
                        SysUserModel.NICKNAME.like(f"%{condition}%"),
                    )
                ).count()
            sys_users = [item.to_all_dict() for item in list_sys_user]
            result = {
                "pageNum": pageNum,
                "pageSize": pageSize,
                "total": total_count,
                "data": sys_users
            }
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")
    # 根据用户名获取数据
    def select_data_by_username(self, session, username):
        try:
            sys_user = session.query(SysUserModel).filter(SysUserModel.USERNAME == username).first()
            return sys_user if sys_user is not None else None
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 插入数据
    def insert_data(self, session, sys_user):
        try:
            session.add(sys_user)
            session.commit()
            return 1
        except exc.SQLAlchemyError as e:
            # error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return 0
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 更新数据
    def update_data(self, session, sys_user, uid):
        try:
            result = session.query(SysUserModel).filter_by(ID=uid).update(sys_user)
            session.commit()
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 删除数据
    def delete_data(self, session, uid):
        try:
            result = session.query(SysUserModel).filter(SysUserModel.ID == uid).delete()
            session.commit()
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 批量删除
    def delete_alldata(self, session, uids):
        try:
            result = session.query(SysUserModel).filter(SysUserModel.ID.in_(uids)).delete()
            session.commit()
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")